--EXEC SP_CUSTOMER_AR_REPORT -1
ALTER PROC SP_CUSTOMER_AR_REPORT( @CUSTOMER_ID INT)
AS
BEGIN
	DECLARE @RESULTS TABLE ( CUST_ID INT,
							 Customer_Name	VARCHAR(65),
							 Payment_Terms  VARCHAR(50),
							 CREDIT_LIMIT	NUMERIC(18,4),
							 AR				NUMERIC(18,4),
							 SHIP_SCHE_7DAYS NUMERIC(18,4),
							 SHIP_SCHE_30DAYS NUMERIC(18,4),
							 TOTAL_BACKLOG  NUMERIC(18,4),
							 HOLD_BACKLOG   NUMERIC(18,4) )
	DECLARE @CUSTOMER_CURSOR CURSOR
	DECLARE @CUST_ID INT
	DECLARE @SHIP_SCHE_7DAYS NUMERIC(18,4)
	DECLARE @SHIP_SCHE_30DAYS NUMERIC(18,4)
	DECLARE @TOTAL_BACKLOG NUMERIC(18,4)
	DECLARE @HOLD_BACKLOG NUMERIC(18,4)
	DECLARE @CURR_DATE DATETIME 

	SET @CURR_DATE = GETDATE()
	IF @CUSTOMER_ID > 0
	BEGIN
		INSERT INTO @RESULTS (CUST_ID, Customer_Name, Payment_Terms, CREDIT_LIMIT,AR)
		SELECT CUSTOMER_ID, C_NAME, C_PAY_TERMS, ISNULL(C_CREDIT_LIMIT,0.0), ISNULL(AR_BALANCE,0.0) FROM CUSTOMER WHERE CUSTOMER_ID IN ( SELECT CUSTOMER_ID FROM SALES_ORDER WHERE SO_STATUS = 'APPROVED' AND CUSTOMER_ID = @CUSTOMER_ID )
	END
	ELSE
	BEGIN
		INSERT INTO @RESULTS (CUST_ID, Customer_Name, Payment_Terms, CREDIT_LIMIT,AR)
		SELECT CUSTOMER_ID, C_NAME, C_PAY_TERMS, ISNULL(C_CREDIT_LIMIT,0.0), ISNULL(AR_BALANCE,0.0) FROM CUSTOMER WHERE CUSTOMER_ID IN ( SELECT CUSTOMER_ID FROM SALES_ORDER WHERE SO_STATUS = 'APPROVED' )
	END
	SET @CUSTOMER_CURSOR = CURSOR FOR SELECT CUST_ID FROM @RESULTS
	OPEN @CUSTOMER_CURSOR
	FETCH NEXT FROM @CUSTOMER_CURSOR INTO @CUST_ID
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SELECT @SHIP_SCHE_7DAYS  = ISNULL(SUM((COOL_QTY*COOL_UNIT_COST)),0.0) FROM CUSTOMER_OPEN_ORDER_LINEITEM COOL, SALES_ORDER SO WHERE COOL.SalesOrder_ID = SO.SalesOrder_ID AND COOL_STATUS = 'APPROVED' AND SO.Customer_Id = @CUST_ID AND ISNULL( COOL_REVISED_DATE, ISNULL( COOL_SCHEDULE_DATE, COOL_REQUESTED_DATE ) )  <= DATEADD( d, 7, @CURR_DATE )
		SELECT @SHIP_SCHE_30DAYS = ISNULL(SUM((COOL_QTY*COOL_UNIT_COST)),0.0) FROM CUSTOMER_OPEN_ORDER_LINEITEM COOL, SALES_ORDER SO WHERE COOL.SalesOrder_ID = SO.SalesOrder_ID AND COOL_STATUS = 'APPROVED' AND SO.Customer_Id = @CUST_ID AND ISNULL( COOL_REVISED_DATE, ISNULL( COOL_SCHEDULE_DATE, COOL_REQUESTED_DATE ) )  <= DATEADD( d, 30, @CURR_DATE )
		SELECT @TOTAL_BACKLOG = ISNULL(SUM((COOL_QTY*COOL_UNIT_COST)),0.0) FROM CUSTOMER_OPEN_ORDER_LINEITEM COOL, SALES_ORDER SO WHERE COOL.SalesOrder_ID = SO.SalesOrder_ID AND COOL_STATUS <> 'SHIPPED' AND SO.Customer_Id = @CUST_ID 
		SELECT @HOLD_BACKLOG = ISNULL(SUM((COOL_QTY*COOL_UNIT_COST)),0.0) FROM CUSTOMER_OPEN_ORDER_LINEITEM COOL, SALES_ORDER SO WHERE COOL.SalesOrder_ID = SO.SalesOrder_ID AND COOL_STATUS = 'HOLD' AND SO.Customer_Id = @CUST_ID 

		UPDATE @RESULTS SET SHIP_SCHE_7DAYS = @SHIP_SCHE_7DAYS, SHIP_SCHE_30DAYS = @SHIP_SCHE_30DAYS, TOTAL_BACKLOG = @TOTAL_BACKLOG, HOLD_BACKLOG = @HOLD_BACKLOG WHERE CUST_ID = @CUST_ID

		SET @SHIP_SCHE_7DAYS = 0.0
		SET @SHIP_SCHE_30DAYS = 0.0
		SET @TOTAL_BACKLOG = 0.0
		SET @HOLD_BACKLOG = 0.0
		FETCH NEXT FROM @CUSTOMER_CURSOR INTO @CUST_ID
	END
	CLOSE @CUSTOMER_CURSOR
	DEALLOCATE @CUSTOMER_CURSOR
	
	SELECT Customer_Name, Payment_Terms,CREDIT_LIMIT, AR, SHIP_SCHE_7DAYS, SHIP_SCHE_30DAYS, TOTAL_BACKLOG, HOLD_BACKLOG  FROM @RESULTS ORDER BY HOLD_BACKLOG DESC, TOTAL_BACKLOG DESC
	
END
